package com.wpi.smarttour.helpers;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.location.Location;

public class SmartTourDBAdapter
{
   private static final String DATABASE_NAME = "smarttour.db";
   private static final int DATABASE_VERSION = 4;
   private static final String DATABASE_TABLE = "gpscords";
   public static final String KEY_ID = "_id";
   public static final String KEY_LONG = "long";
   public static final String KEY_LAT = "lat";
   public static final String KEY_COMPASS = "compass";
   public static final String KEY_FILE = "file";
   public static final String KEY_TIME = "time";
   public static final String KEY_RECORDING = "recording";
   public static final String KEY_X = "x";
   public static final String KEY_Y = "y";
   private static final String DATABASE_TABLE_2 = "sepostions";
   // public static final String KEY_ID = "_id";
   // public static final String KEY_FILE = "file";
   public static final String KEY_STARTING_LOCATION_X = "sxlocation";
   public static final String KEY_STARTING_LOCATION_Y = "sylocation";
   public static final String KEY_ENDING_LOCATION_X = "exlocation";
   public static final String KEY_ENDING_LOCATION_Y = "eylocation";
   public static final String KEY_DISTANCE = "distance";
   public static final String KEY_STARTING_TIME = "stime";
   public static final String KEY_ENDING_TIME = "etime";
   private static final String DATABASE_TABLE_3 = "videonodes";
   // public static final String KEY_ID = "_id";
   // public static final String KEY_FILE = "file";
   public static final String KEY_STARTING_NODE = "startingnode";
   public static final String KEY_ENDING_NODE = "endingnode";
   // public static final String KEY_STARTING_TIME = "stime";
   // public static final String KEY_ENDING_TIME = "etime";
   private SQLiteDatabase db;
   private final Context context;
   private smartTourDBOpenHelper dbHelper;

   public SmartTourDBAdapter(Context _context)
   {
      this.context = _context;
      dbHelper = new smartTourDBOpenHelper(context, DATABASE_NAME, null, DATABASE_VERSION);
   }

   public void close()
   {
      db.close();
   }

   public void open() throws SQLiteException
   {
      Utilities.LogDebug("SmartTourDBAdapter-open()", "Opening Database");
      try
      {
         db = dbHelper.getWritableDatabase();
      }
      catch (SQLiteException ex)
      {
         db = dbHelper.getReadableDatabase();
         Utilities.LogError("DBAdapter-Open", ex);
      }
      Utilities.LogDebug("Exiting Open Database");
   }

   // ---------------------------------------------------TABLE 1
   // -------------------------------------------------------------------------------------------
   // Insert a new location
   public long insertLocation(Location _location, String _fileName, boolean _recording, long _time)
   {
      // Create a new row of values to insert.
      Utilities.LogDebug("SmartTourDBAdapter-intersetLocation: Trying to Save");
      ContentValues newLocation = new ContentValues();
      // Assign values for each row.
      newLocation.put(KEY_LONG, _location.getLongitude());
      newLocation.put(KEY_LAT, _location.getLatitude());
      newLocation.put(KEY_COMPASS, _location.getBearing());
      newLocation.put(KEY_FILE, _fileName);
      newLocation.put(KEY_RECORDING, _recording);
      newLocation.put(KEY_TIME, _time);
      newLocation.put(KEY_X, 0);
      newLocation.put(KEY_Y, 0);
      // Insert the row.
      return db.insert(DATABASE_TABLE, null, newLocation);
   }

   // Remove a task based on its index
   public boolean removeLocation(long _rowIndex)
   {
      return db.delete(DATABASE_TABLE, KEY_ID + "=" + _rowIndex, null) > 0;
   }

   // Update a task
   public boolean updateLocation(long _rowIndex, Location _location, long _time)
   {
      ContentValues newValue = new ContentValues();
      newValue.put(KEY_LONG, _location.getLongitude());
      newValue.put(KEY_LAT, _location.getLatitude());
      newValue.put(KEY_COMPASS, _location.getBearing());
      return db.update(DATABASE_TABLE, newValue, KEY_ID + "=" + _rowIndex, null) > 0;
   }

   public boolean updateFileName(long _rowIndex, String _fileName)
   {
      ContentValues newValue = new ContentValues();
      newValue.put(KEY_FILE, _fileName);
      return db.update(DATABASE_TABLE, newValue, KEY_ID + "=" + _rowIndex, null) > 0;
   }

   public boolean updateXY(long _rowIndex, int x, int y)
   {
      ContentValues newValue = new ContentValues();
      newValue.put(KEY_X, x);
      newValue.put(KEY_Y, y);
      return db.update(DATABASE_TABLE, newValue, KEY_ID + "=" + _rowIndex, null) > 0;
   }

   public Cursor getAllLocationItemsCursor()
   {
      return db.query(DATABASE_TABLE, new String[]{KEY_ID, KEY_LONG,
            KEY_LAT, KEY_COMPASS, KEY_TIME}, null, null, null, null, null);
   }

   public Cursor getAllLocationItemsPerVideo(String videoFile) throws SQLException
   {
      String where = KEY_FILE + "=" + videoFile + " AND " + KEY_RECORDING + "=1";
      String order = KEY_TIME;
      Cursor temp;
      try
      {
         temp = db.query(DATABASE_TABLE, new String[]{KEY_ID, KEY_LONG, KEY_LAT, KEY_COMPASS, KEY_TIME}, where, null, null, null, order);
      }
      catch (SQLException sqle)
      {
         Utilities.LogDebug("STDA-getAllLocationItemsPerVideo", "ERROR");
         Utilities.LogError("STDA-getAllLocationItemsPerVideo", sqle);
         throw sqle;
      }
      return temp;
   }

   public Cursor setCursorToLocationItem(long _rowIndex) throws SQLException
   {
      Cursor result = db.query(true, DATABASE_TABLE, new String[]{KEY_ID,
            KEY_LONG, KEY_LAT, KEY_COMPASS}, KEY_ID + "=" + _rowIndex,
            null, null, null, null, null);
      if ((result.getCount() == 0) || !result.moveToFirst())
      {
         throw new SQLException("No items found for row: " + _rowIndex);
      }
      return result;
   }

   public Location getLocationItem(long _rowIndex) throws SQLException
   {
      Cursor cursor = db.query(true, DATABASE_TABLE, new String[]{KEY_ID,
            KEY_LONG, KEY_LAT, KEY_COMPASS}, KEY_ID + "=" + _rowIndex,
            null, null, null, null, null);
      if ((cursor.getCount() == 0) || !cursor.moveToFirst())
      {
         throw new SQLException("No to do item found for row: " + _rowIndex);
      }
      double clat = Double.parseDouble(cursor.getString(cursor
            .getColumnIndex(KEY_LAT)));
      double clong = Double.parseDouble(cursor.getString(cursor
            .getColumnIndex(KEY_LONG)));
      long compass = Long.parseLong(cursor.getString(cursor
            .getColumnIndex(KEY_COMPASS)));
      Location result = new Location("GPS");
      result.setLatitude(clat);
      result.setLongitude(clong);
      result.setBearing(compass);
      return result;
   }

   public Cursor getAllReplaceableFileName(String _fileName) throws SQLException
   {
      Utilities.LogDebug("STDA-getAllReplaceableFileName", "Starting Query");
      String where = KEY_FILE + "=" + _fileName;
      String order = KEY_TIME;
      Cursor temp;
      try
      {
         temp = db.query(DATABASE_TABLE, new String[]{KEY_ID, KEY_LAT, KEY_LONG}, where, null, null, null, order);
      }
      catch (SQLException sqle)
      {
         Utilities.LogDebug("STDA-getAllReplaceableFileName", "ERROR");
         Utilities.LogError("STDA-getAllReplaceableFileName", sqle);
         throw sqle;
      }
      return temp;
   }

   public Cursor getAllXYFromFileName(String _fileName) throws SQLException
   {
      Utilities.LogDebug("STDA-getAllXYFromFileName", "Starting Query");
      String where = KEY_FILE + "=" + _fileName + "AND " + KEY_RECORDING + "=1";
      String order = KEY_TIME;
      Cursor temp;
      try
      {
         temp = db.query(DATABASE_TABLE, new String[]{KEY_ID, KEY_X, KEY_Y, KEY_TIME}, where, null, null, null, order);
      }
      catch (SQLException sqle)
      {
         Utilities.LogDebug("STDA-getAllReplaceableFileName", "ERROR");
         Utilities.LogError("STDA-getAllReplaceableFileName", sqle);
         throw sqle;
      }
      return temp;
   }

   // ----------------------------------------------------------TABLE 2
   // -------------------------------------------------------
   // selocation table - get all video tags
   public Cursor getAllSELocationItems() throws SQLException
   {
      Cursor temp;
      Utilities.LogDebug("STDA-getAllSELocationItems", "Starting Query");
      try
      {
         temp = db.query(DATABASE_TABLE_2, new String[]{KEY_ID, KEY_FILE, KEY_STARTING_LOCATION_X, KEY_STARTING_LOCATION_Y,
               KEY_ENDING_LOCATION_X, KEY_ENDING_LOCATION_Y, KEY_STARTING_TIME, KEY_ENDING_TIME}, null, null, null, null, null);
      }
      catch (SQLException sqle)
      {
         Utilities.LogDebug("STDA-getAllSELocationItems", "ERROR");
         Utilities.LogError("STDA-getAllSELocationItems", sqle);
         throw sqle;
      }
      return temp;
   }

   // selocation table - get all video tags
   public Cursor getStartingTimeForFile(String db_fileName) throws SQLException
   {
      Utilities.LogDebug("STDA-getAllSELocationItems", "Starting Query");
      String where = KEY_FILE + " = " + db_fileName;
      Utilities.LogDebug("STDA-getAllSELocationItems", "WHERE: " + where);
      String order = KEY_STARTING_TIME;
      Cursor temp;
      try
      {
         temp = db.query(DATABASE_TABLE_2, new String[]{KEY_ID, KEY_FILE, KEY_STARTING_TIME, KEY_ENDING_TIME}, where, null, null, null, order);
         Utilities.LogDebug("STDA-getAllSELocationItems", "Finished");
      }
      catch (SQLException sqle)
      {
         Utilities.LogDebug("STDA-getStartingTimeForFile", "ERROR");
         Utilities.LogError("STDA-getStartingTimeForFile", sqle);
         throw sqle;
      }
      return temp;
   }

   // Insert a new row to the selocation table
   public long insertXYPerVideo(String _fileName, int startingX, int startingY, int endingX, int endingY, long startingTime, long endingTime, double distance)
   {
      // Create a new row of values to insert.
      Utilities.LogDebug("SmartTourDBAdapter-insertXYPerVideo: Saving");
      ContentValues newInsert = new ContentValues();
      // Assign values for each row.
      newInsert.put(KEY_FILE, _fileName);
      newInsert.put(KEY_STARTING_LOCATION_X, startingX);
      newInsert.put(KEY_STARTING_LOCATION_Y, startingY);
      newInsert.put(KEY_ENDING_LOCATION_X, endingX);
      newInsert.put(KEY_ENDING_LOCATION_Y, endingY);
      newInsert.put(KEY_DISTANCE, distance);
      newInsert.put(KEY_STARTING_TIME, startingTime);
      newInsert.put(KEY_ENDING_TIME, endingTime);
      // Insert the row.
      return db.insert(DATABASE_TABLE_2, null, newInsert);
   }

   // ---------------------------------------------------------------TABLE 3
   // -----------------------------------------------------
   public long insertVideoNode(String _fileName, int _startingNode, int _endingNode, long _startingTime, long _endingTime)
   {
      Utilities.LogDebug("SmartTourDBAdapter-insertVideoNode: Saving Video Node");
      ContentValues newInsert = new ContentValues();
      // Assign values for each row.
      newInsert.put(KEY_FILE, _fileName);
      newInsert.put(KEY_STARTING_NODE, _startingNode);
      newInsert.put(KEY_ENDING_NODE, _endingNode);
      newInsert.put(KEY_STARTING_TIME, _startingTime);
      newInsert.put(KEY_ENDING_TIME, _endingTime);
      return db.insert(DATABASE_TABLE_3, null, newInsert);
   }

   // Return all VideoNodes
   public Cursor getAllVideoNodes() throws SQLException
   {
      String order = KEY_STARTING_TIME + " DESC";
      Cursor temp;
      Utilities.LogDebug("STDA-getAllVideoNodes", "Starting Query");
      try
      {
         temp = db.query(DATABASE_TABLE_3, new String[]{KEY_ID, KEY_FILE, KEY_STARTING_NODE, KEY_ENDING_NODE,
               KEY_STARTING_TIME, KEY_ENDING_TIME}, null, null, null, null, order);
      }
      catch (SQLException sqle)
      {
         Utilities.LogDebug("STDA-getAllVideoNodes", "ERROR");
         Utilities.LogError("STDA-getAllVideoNodes", sqle);
         throw sqle;
      }
      return temp;
   }

   // ----------------------------------------------------------------------------------------------------------------------------
   private static class smartTourDBOpenHelper extends SQLiteOpenHelper
   {
      public smartTourDBOpenHelper(Context context, String name, CursorFactory factory, int version)
      {
         super(context, name, factory, version);
      }

      // SQL Statement to create a new database.
      private static final String DATABASE_CREATE = "create table "
         + DATABASE_TABLE + " (" + KEY_ID
         + " integer primary key autoincrement, " + KEY_LONG
         + " double not null, " + KEY_LAT + " double not null, "
         + KEY_COMPASS + " long, " + KEY_FILE + " text, " + KEY_RECORDING + " boolean, " + KEY_X + " int, " + KEY_Y + " int, " + KEY_TIME + " long);";
      private static final String DATABASE_CREATE_TABLE2 = "create table "
         + DATABASE_TABLE_2 + " (" + KEY_ID
         + " integer primary key autoincrement, " + KEY_FILE + " text not null, " + KEY_STARTING_LOCATION_X + " int not null, " + KEY_STARTING_LOCATION_Y + " int not null, "
         + KEY_ENDING_LOCATION_X + " int not null, " + KEY_ENDING_LOCATION_Y + " int not null, "
         + KEY_DISTANCE + " double, " + KEY_STARTING_TIME + " long, " + KEY_ENDING_TIME + " long);";
      private static final String DATABASE_CREATE_TABLE3 = "create table "
         + DATABASE_TABLE_3 + " (" + KEY_ID
         + " integer primary key autoincrement, " + KEY_FILE + " text not null, " + KEY_STARTING_NODE + " int, " + KEY_ENDING_NODE + " int, "
         + KEY_STARTING_TIME + " long, " + KEY_ENDING_TIME + " long);";

      @Override
      public void onCreate(SQLiteDatabase _db)
      {
         Utilities.LogDebug("SmartTourDBAdapter-onCreate", "Creating Tables");
         _db.execSQL(DATABASE_CREATE);
         _db.execSQL(DATABASE_CREATE_TABLE2);
         _db.execSQL(DATABASE_CREATE_TABLE3);
      }

      @Override
      public void onUpgrade(SQLiteDatabase _db, int _oldVersion, int _newVersion)
      {
         Utilities.LogWarning("SmartTourDBAdapter" + " Upgrading from version " + _oldVersion
               + " to " + _newVersion
               + ", which will destroy all old data");
         // Drop the old table.
         _db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE);
         _db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE_2);
         _db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE_3);
         // Create a new one.
         onCreate(_db);
      }
   }
}